package com.pointi.emoGatewayM;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.pointi.emoTools.emoJDBC;
import com.pointi.emoTools.emoReceived;
import com.pointi.emoTools.infoStatus;

public class JDBC extends emoJDBC
{
	//____________________________________________________________________________________________________
	// Default

	ConfigurationData conf_data ;
	public void SetDatabaseParam(ConfigurationData db_param)
	{
		super.SetDatabaseParam(db_param) ;
		conf_data = db_param ; // TYPE_CONVERT / UPCASTING
	}

	public void fnPrepareDatabase()
	{
		String query = "" ;
		query = " EXEC sp_create_info_agent " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_info_customer " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_callkey_yyyymm " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_callkey_recfile_yyyymm " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_callkey_checklist_yyyymm " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_info_server " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_info_client " ; 
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_gw_work_scheduler " ; 
		fnExecuteInsertOrUpdateQuery(query) ;

		// TODO for DEMO | Dummy Data
		for (int i=1; i<2; i++)
		{
			query = " INSERT INTO t_info_server (server_type, server_no, server_ip, server_dns, server_port, server_alive, server_status, server_count, regdate) " +
					" VALUES ('type', " + String.valueOf(i) + ", '127.0.0.1', '127.0.0.2', '9999', 1, 1, 10, '" + fnGetRegDate() + "') " ;
			fnExecuteInsertOrUpdateQuery(query) ;
			query = " INSERT INTO t_info_client (client_no, client_alive, client_status, client_count, regdate) " +
					" VALUES (" + String.valueOf(i) + ", 1, 1, 10, '" + fnGetRegDate() + "') " ;
			fnExecuteInsertOrUpdateQuery(query) ;
		}		
		sleep(500) ;
	}

	public void fnPrepareNextDay()
	{
		String query = "" ;
		query = " EXEC sp_create_callkey_yyyymm " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_callkey_recfile_yyyymm " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_callkey_checklist_yyyymm " ;
		fnExecuteInsertOrUpdateQuery(query) ;
		sleep(50) ;
	}

	// ____________________________________________________________________________________________________
	// t_info_agent

	public int fnInsertAgentInfo(String inID, String inName, String inExtNo)
	{
		int agentSeq = 0 ;
		PreparedStatement mPS = null ;
		ResultSet mRS = null ;
		String query = "" ;
		// 2014.03.11 | AGENT INFO | LJJ
		//*// INSERT ONLY -> INSERT, IF NOT EXISTS
		query = " INSERT INTO t_info_agent ( uid , name , ext , use_yn , regdate ) " + 
				" VALUES ( '" + inID + "', '" + inName + "', '" + inExtNo + "', " + "0" + ", '" + fnGetRegDate() + "') " +
				" SELECT SCOPE_IDENTITY() AS 'seq' ; " ;
		/*/
		query = " IF NOT EXISTS (SELECT * FROM t_agent_info WHERE ext = " + inExtNo + ") " +
				" BEGIN " +
					" INSERT INTO t_info_agent ( uid , name , ext , use_yn , regdate ) " + 
					" VALUES ( '" + inID + "', '" + inName + "', '" + inExtNo + "', '" + "0" + "', '" + fnGetRegDate() + "') " +
					" SELECT SCOPE_IDENTITY() AS 'seq' " +
				" END " +
				" ELSE BEGIN " +
					" UPDATE t_info_agent " +
					" SET uid = " + inID + ", name = '" + inName + "', ext = '" + inExtNo + "', regdate = '" + fnGetRegDate() + "' " +
					" WHERE uid = " + inID + " " +
					" SELECT SCOPE_IDENTITY() AS 'seq' " +
				" END " ;
		//*/
		try {
			mPS = mConnection.prepareStatement(query) ;
			mPS.executeUpdate() ;
			mRS = mPS.getGeneratedKeys() ;
			while (mRS.next()) { agentSeq = mRS.getInt("seq") ; }
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mPS != null) mPS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return agentSeq ;
	}

	// ____________________________________________________________________________________________________
	// t_info_customer

	public int fnInsertCustomerInfo(String inName, String inJumin, String inPhone)
	{
		int customerSeq = 0 ;
		PreparedStatement mPS = null ;
		ResultSet mRS = null ;
		String query = "" ;
		// 2014.03.11 | CUSTOMER INFO | LJJ
		//*// INSERT ONLY -> INSERT IF NOT EXISTS
		query = " INSERT INTO t_info_customer ( cname , cjumin , ccellphone , use_yn , regdate ) " + 
				" VALUES ( '" + inName + "', '" + inJumin + "', '" + inPhone + "', " + "0" + ", '" + fnGetRegDate() + "') " +
				" SELECT SCOPE_IDENTITY() AS 'seq' ; " ;
		/*/
		query = " IF NOT EXISTS (SELECT * FROM t_customer_info WHERE ccellphone = " + inPhone + ") " +
				" BEGIN " +
					" INSERT INTO t_customer_info ( cname , cjumin , ccellphone , use_yn , regdate ) " + 
					" VALUES ( '" + inName + "', '" + inJumin + "', '" + inPhone + "', '" + "0" + "', '" + fnGetRegDate() + "') " +
					" SELECT SCOPE_IDENTITY() AS 'seq' " +
				" END " +
				" ELSE BEGIN " +
					" UPDATE t_info_customer " +
					" SET cname = '" + inName + "', cjumin = '" + inJumin + "', ccellphone = '" + inPhone + "', regdate = '" + fnGetRegDate() + "' " +
					" WHERE cjumin = '" + inJumin + "' " +
					" SELECT SCOPE_IDENTITY() AS 'seq' " +
				" END " ;
		//*/
		try {
			mPS = mConnection.prepareStatement(query) ;
			mPS.executeUpdate() ;
			mRS = mPS.getGeneratedKeys() ;
			while (mRS.next()) { customerSeq = mRS.getInt("seq") ; }
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mPS != null) mPS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return customerSeq ;
	}

	// ____________________________________________________________________________________________________
	// t_callkey_yyyymm

	public int fnCreateCallKey(String inCallStartTime, int inAgentSeq, int inCustomerSeq)
	{
		int callkeySeq = 0 ;
		String rdate = this.fnConvertDateTime(inCallStartTime, 1) ;
		String rtime = this.fnConvertDateTime(inCallStartTime, 2) ;
		PreparedStatement mPS = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " INSERT INTO t_callkey_" + fnGetYYYYMM() + " ( rdate , rtime , agent , customer , regdate ) " + 
				" VALUES ( '" + rdate + "', '" + rtime + "', " + String.valueOf(inAgentSeq) + ", " + String.valueOf(inCustomerSeq) + ", '" + fnGetRegDate() + "') " +
				" SELECT SCOPE_IDENTITY() AS 'seq' ; " ;
		try {
			mPS = mConnection.prepareStatement(query) ;
			mPS.executeUpdate() ;
			mRS = mPS.getGeneratedKeys() ;
			while (mRS.next()) { callkeySeq = mRS.getInt("seq") ; }
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mPS != null) mPS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return callkeySeq ;
	}

	// ____________________________________________________________________________________________________
	// t_callkey_recfile_yyyymm

	public emoReceived fnGetReceivedInfo(String inCallKey)
	{
		ResultSet mRS = null ;
		emoReceived mReceived = null ;
		String query = "" ;
		query = " SELECT file_path_org, file_name_org, file_path_tgt, file_name_tgt " +
				" FROM t_callkey_recfile_" + fnGetYYYYMM() +
				" WHERE callkey = " + Integer.valueOf(inCallKey) + " ; " ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					mReceived = new emoReceived(
							mRS.getString("file_path_org"),
							mRS.getString("file_name_org"),
							mRS.getString("file_path_tgt"),
							mRS.getString("file_name_tgt")) ;
				}
		} catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return mReceived ;
	}

	public void fnInsertFileInfo(int inCallKey, String inFilePath, String inFileName) // XXX
	{
		String _FileDownloadPath = inFilePath ;
		String _FileTargetPath = conf_data.s_gateway_tgt_path ;
		String query = "" ;
		query = " INSERT INTO t_callkey_recfile_" + fnGetYYYYMM() + " ( callkey, speaker_no, file_no, file_path_org, file_name_org, file_path_tgt, file_name_tgt, filekey ) " + 
				" VALUES ( " + String.valueOf(inCallKey) + ", " + "0" + ", " + "0" + ", '" + _FileDownloadPath + "', '" + inFileName + "', '" + _FileTargetPath + "', '" + inFileName + "', '" + inFileName.replace(".wav", "") + "') " ; 
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	public int fnGetCallKeyByFileName(String inFileName)
	{
		ResultSet mRS = null ;
		int callKey = 0 ;
		String query = "" ;
		query = " SELECT callkey " + 
				" FROM t_callkey_recfile_" + fnGetYYYYMM() + " " +
				" WHERE file_name_org = '" + inFileName + "' ; " ;
		try {
			if ((mRS = this.fnGetResultSet(query)) != null)
				while (mRS.next()) { callKey = mRS.getInt("callkey") ; }
		} catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return callKey ;
	}

	// ____________________________________________________________________________________________________
	// t_callkey_checklist_yyyymm

	public void fnGetReceivedList(ArrayList<String> mReceivedFile)
	{
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT callkey " +
				" FROM t_callkey_checklist_" + fnGetYYYYMM() +
				" WHERE check_receiver = 3 and check_converter = 0 ; " ; // check_converter != 3
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					String mCallKey = String.valueOf(mRS.getInt("callkey")) ;
					if (!mReceivedFile.contains(mCallKey)) mReceivedFile.add(mCallKey) ;
				}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	public void fnInsertCheckList(int inCallKey)
	{
		String query = "" ;
		query = " INSERT INTO t_callkey_checklist_" + fnGetYYYYMM() + " ( callkey, check_receiver, check_converter, check_client, check_engine, used, used_v2, used_v3, check_history, check_proceed, check_kss, check_research, check_weight, check_satisfact, check_sys_cracked, check_man_cracked, check_exception_ai ) " +
				" VALUES ( " + String.valueOf(inCallKey) + ", '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "', '" + "0" + "') " +
				" SELECT SCOPE_IDENTITY() AS 'seq' ; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	public void fnUpdateCheckList(String inColumn, int inValue, int inCallKey)
	{
		String query = "" ;
		query = " UPDATE t_callkey_checklist_" + fnGetYYYYMM() + " " +
				" SET " + inColumn + " = '" + inValue + "' " +
				" WHERE callkey = " + inCallKey ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	// ________________________________________________
	// t_callkey_checklist_yyyymm , t_gw_work_scheduler

	public void fnGetConvertedList(ArrayList<String> mConvertedFile)
	{
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT A.callkey " +
				" FROM t_callkey_checklist_" + fnGetYYYYMM() + " A " +
				" LEFT OUTER JOIN t_gw_work_scheduler B on A.callkey = B.callkey " +
				" WHERE B.callkey IS NULL AND A.check_converter = 3 AND A.check_client = 0 ; " ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					String mCallKey = String.valueOf(mRS.getInt("callkey")) ;
					if (!mConvertedFile.contains(mCallKey)) mConvertedFile.add(mCallKey) ;
				}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	// ____________________________________________________________________________________________________
	// t_gw_work_scheduler

	public void fnInsertWorkSchedule(String inCallKey, String inClient, String inServer)
	{
		String query = "" ;
		query = "INSERT INTO t_gw_work_scheduler (callkey, client_no, server_no, regdate) " + 
				"VALUES ( " + inCallKey + ", " + inClient + ", " + inServer + ", '" + fnGetRegDate() + "' )" ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	// ____________________________________________________________________________________________________
	// t_info_server

	public int fnGetInfoServer(infoStatus [] inServer)
	{
		ResultSet mRS = null ;
		int IDX = 0 ;
		String query = "" ;
		query = " SELECT * FROM t_info_server " ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					inServer[IDX].seq = mRS.getInt("seq") ;
					inServer[IDX].no = mRS.getInt("no") ;
					inServer[IDX].alive = mRS.getInt("alive") ;
					inServer[IDX].status = mRS.getInt("status") ;
					inServer[IDX].count = mRS.getInt("count") ;
					IDX ++ ;
				}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return IDX ;
	}

	public void fnGetServerStatus(ArrayList<String> mAliveServer)
	{
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT server_no " +
				" FROM t_info_server " + 
				" WHERE server_alive = 1" ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					String mServerNo = String.valueOf(mRS.getInt("server_no")) ;
					if (!mAliveServer.contains(mServerNo)) mAliveServer.add(mServerNo) ;
				}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	public void fnUpdateInfoServer(infoStatus inServer)
	{
		String query = "" ;
		query = " UPDATE t_info_server " +
				" SET server_status = " + inServer.status + ", " + 
				" server_alive = " + inServer.alive +
				" WHERE server_no = " + inServer.no ;
		fnExecuteInsertOrUpdateQuery(query) ;	
	}

	public void fnReassignServer(String inServerNo)
	{
		int _NONE = 65432 ;
		int candidateA = _NONE ; // Alive Server : First of All
		int candidateB = _NONE ; // Alive Server : Next to Current
		int mServerNo = Integer.valueOf(inServerNo) ; ;

		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT server_no " +
				" FROM t_info_server " +
				" WHERE server_alive = 1 " ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					int tServerNo = mRS.getInt("server_no") ;
					if (candidateA == _NONE) { candidateA = tServerNo ; }
					else if (tServerNo > mServerNo && candidateB == _NONE) { candidateB = tServerNo ; break ; }
				}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		if (candidateB != _NONE) { mServerNo = candidateB ; }
		else { mServerNo = candidateA ; }
		query = " UPDATE t_gw_work_scheduler " +
				" SET server_no = " + String.valueOf(mServerNo) + " " +
				" WHERE server_no = " + inServerNo + "; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}
	public void fnReassignServer(int inServerNo) { this.fnReassignServer(String.valueOf(inServerNo)) ; }

	// ____________________________________________________________________________________________________
	// t_info_client

	public int fnGetInfoClient(infoStatus [] inClient)
	{
		int IDX = 0 ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT * FROM t_info_client " ;
		try {
			if ((mRS = fnGetResultSet(query)) != null)
				while (mRS.next()) {
					inClient[IDX].seq = mRS.getInt("seq") ;
					inClient[IDX].no = mRS.getInt("no") ;
					inClient[IDX].alive = mRS.getInt("alive") ;
					inClient[IDX].status = mRS.getInt("status") ;
					inClient[IDX].count = mRS.getInt("count") ;
					IDX ++ ;
				}		
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return IDX ;
	}

	public void fnUpdateInfoClient(infoStatus inClient)
	{
		String query = "" ;
		query = " UPDATE t_info_client " +
				" SET client_status = " + inClient.status + ", " + 
				" client_alive = " + inClient.alive +
				" WHERE client_no = " + inClient.no ;
		fnExecuteInsertOrUpdateQuery(query) ;	
	}

	public void fnGetClientStatus(ArrayList<String> mAliveClient)
	{
		ResultSet mRS = null ;
		String query = "SELECT client_no FROM t_info_client WHERE client_alive = 1" ;
		try {
			if ((mRS = fnGetResultSet(query)) != null) {
				while (mRS.next()) {
					String mClientNo = String.valueOf(mRS.getInt("client_no")) ;
					if (!mAliveClient.contains(mClientNo)) { mAliveClient.add(mClientNo) ; }
				}
			}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	public void fnReassignClient(String inClientNo)
	{
		int _NONE = 9999 ;
		int candidateA = _NONE ; // Alive Client : First of All
		int candidateB = _NONE ; // Alive Client : Next to Current
		int mClientNo = Integer.valueOf(inClientNo) ; ;
		ResultSet mRS = null ;
		String query = "SELECT server_no FROM t_info_client WHERE server_alive = 1" ;
		try {
			if ((mRS = fnGetResultSet(query)) != null) {
				while (mRS.next()) {
					int tClientNo = mRS.getInt("server_no") ;
					if (candidateA == _NONE) { candidateA = tClientNo ; }
					else if (tClientNo > mClientNo && candidateB == _NONE) { candidateB = tClientNo ; break ; }
				}
			}
		}
		catch (SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		if (candidateB != _NONE) { mClientNo = candidateB ; }
		else { mClientNo = candidateA ; }
		query = " UPDATE t_gw_work_scheduler " +
				" SET server_no = " + String.valueOf(mClientNo) + " " +
				" WHERE server_no = " + inClientNo + "; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}
	public void fnReassignClient(int inClientNo) { this.fnReassignClient(String.valueOf(inClientNo)) ; }
}
